import pymysql


# 设计数据库工具类
class DBUtil:
    conn = None
    curs = None

    # 创建链接
    @classmethod
    def __creat_conn(cls):
        if cls.conn is None:
            cls.conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                                       password="iHRM_student_2022", database="ihrm",
                                       charset="utf8")
        return cls.conn

    # 关闭链接
    @classmethod
    def __close_conn(cls):
        if cls.conn is not None:
            cls.conn.close()
            cls.conn = None

    # 设计工具里的方法类
    # 设计一个查询语句的通用方法
    @classmethod
    def select_one(cls, sql):
        curs = None
        one = None
        try:
            # 创建链接
            cls.conn = cls.__creat_conn()
            # 创建游标
            curs = cls.conn.cursor()
            # 执行sql
            curs.execute(sql)
            # 获取结果集
            one = curs.fetchone()

        except Exception as err:
            print(f"错误信息是：{err}")

        finally:
            # 关闭游标
            curs.close()
            # 关闭链接
            cls.__close_conn()
            # 返回结果集
            return one

    # 设计一个增删改语句的通用方法
    @classmethod
    def uid_db(cls, sql):
        curs = None
        rows = None
        try:
            # 创建链接
            cls.conn = cls.__creat_conn()
            # 创建游标
            curs = cls.conn.cursor()
            # 执行sql
            curs.execute(sql)
            # 提交事务
            cls.conn.commit()
            # 影响行数
            rows = cls.conn.affected_rows()

        except Exception as err:
            print(f"错误信息是：{err}")
            cls.conn.rollback()

        finally:
            # 关闭游标
            curs.close()
            # 关闭链接
            cls.__close_conn()
            # 返回影响行数
            return f"影响行数是：{rows}"


if __name__ == '__main__':
    one_select = DBUtil.select_one("select * from t_book where id=1;")
    print(one_select)

    one_uid = DBUtil.uid_db("insert into t_book(id,title,pub_date) value(10066666,'冬瓜山香肠','2022-12-18')")
    print(one_uid)